Integrations

How to make PostgreSQL your database backend instead of Microsoft SQL Server.

Before you begin

Creating a new database user

1. Install PostgreSQL on your server. Your server does not have to be a Windows server.
2. Create a database user on the PostgreSQL server with administrator privileges.
3. Create an empty database with your newly created user. Name it whatever you like.
4. Your PostgreSQL server must support the following extensions. If you are using Ubuntu, you will have to install the postgresql-contrib package.
   1. pgcrypto http://www.postgresql.org/docs/9.4/static/pgcrypto.html
   2. uuid-ossp http://www.postgresql.org/docs/9.4/static/uuid-ossp.html
   3. postgres_fdw http://www.postgresql.org/docs/9.4/static/postgres-fdw.html
5. If you are running your PostgreSQL server on another machine than the Atomia applications, you will have to configure PostgreSQL to allow remote connections. Read more about this here: http://www.postgresql.org/docs/9.4/static/auth-pg-hba-conf.html

Configuring unattended.ini

The following settings needs to be done in the unattended.ini file. Place all the settings under the [General] section.

1. 1. Set both EnableMsSqlDeployment and  EnablePostgreSqlDeployment to true to make the installer install on both your Microsoft SQL Server and your PostgreSQL server .

  EnableMsSqlDeployment = true/false
  EnablePostgreSqlDeployment = true/false

1. 1. Add the following connection strings using the previously created user with administrator privileges, see Creating a new database user.

AddtConnectionString = Server=<your server>;database=<your default database>;user id=<your username>;password=<your password>;
  ActionTrailConnectionString = Server=<your server>;database=atomiaactiontrail;user id=<your username>;password=<your password>;
  AutomaionServerConnectionString = Server=<your server>;database=atomiaprovisioning2;user id=<your username>;password=<your password>;
  AccountConnectionString = Server=<your server>;database=atomiaaccount;user id=<your username>;password=<your password>;
  BillingConnectionString = Server=<your server>;database=atomiabilling;user id=<your username>;password=<your password>;
  IdentityConnectionString = Server=<your server>;database=atomiaidentity;user id=<your username>;password=<your password>;
  UserManagementConnectionString = Server=<your server>;database=atomiausermanagement;user id=<your username>;password=<your password>;

3. 3. Set this option to make Action Trail tell that you are using PostgreSQL instead of Microsoft SQL Server.

 ActionTrailLogDataAccessorType = Atomia.ActionTrail.Data.LogDataAccessorPostgreSql, Atomia.ActionTrail.Data
 ActionTrailAuditActionTypesDataAccessorType = Atomia.ActionTrail.Data.AuditActionTypesDataAccessorPostgreSql, Atomia.ActionTrail.Data

3. 3. Leave the following options empty, as they are needed to prevent the connection strings from using System.Data.SqlClient in Atomia Identity. Please note that it should be IdentityProviderName =  with a space after the equal sign on both of the options.

  IdentityProviderName = 
  UserManagementProviderName = 

3. 3. These two settings tells Atomia Identity to use the PostgreSQL membership provider and role providers.

  MembershipProviderType = pgProvider.pgMembershipProvider, pgProvider
  RoleProviderType = pgProvider.pgRoleProvider, pgProvider

3. 3. Set the following three options to let NHibernate know that you intend to use a PostgreSQL server. The NhibernatePrepareSql = false is needed since Atomia can’t use prepared SQL statements with our current version of .NET.

  NhibernateDialect = NHibernate.Dialect.PostgreSQL82Dialect
  NhibernateDriver = NHibernate.Driver.NpgsqlDriver
  NhibernatePrepareSql = false

3. 3. Make sure you have made all the following settings in the unattended.ini file.

 [General]
    EnableMsSqlDeployment = false
    EnablePostgreSqlDeployment = true
    AddtConnectionString = Server=<your server>;database=<your default database>;user id=<your username>;password=<your password>;
    ActionTrailConnectionString = Server=<your server>;database=atomiaactiontrail;user id=<your username>;password=<your password>;
    AutomaionServerConnectionString = Server=<your server>;database=atomiaprovisioning2;user id=<your username>;password=<your password>;
    AccountConnectionString = Server=<your server>;database=atomiaaccount;user id=<your username>;password=<your password>;
    BillingConnectionString = Server=<your server>;database=atomiabilling;user id=<your username>;password=<your password>;
    IdentityConnectionString = Server=<your server>;database=atomiaidentity;user id=<your username>;password=<your password>;
    UserManagementConnectionString = Server=<your server>;database=atomiausermanagement;user id=<your username>;password=<your password>;
    ActionTrailLogDataAccessorType = Atomia.ActionTrail.Data.LogDataAccessorPostgreSql, Atomia.ActionTrail.Data
    ActionTrailAuditActionTypesDataAccessorType = Atomia.ActionTrail.Data.AuditActionTypesDataAccessorPostgreSql, Atomia.ActionTrail.Data
    IdentityProviderName = 
    UserManagementProviderName = 
    MembershipProviderType = pgProvider.pgMembershipProvider, pgProvider
    RoleProviderType = pgProvider.pgRoleProvider, pgProvider
    NhibernateDialect = NHibernate.Dialect.PostgreSQL82Dialect
    NhibernateDriver = NHibernate.Driver.NpgsqlDriver
    NhibernatePrepareSql = false

Installing applications

After completing the steps above, installation can be done. You need to install the database related applications in the following order:

1. Atomia Setup Tools
2. Atomia Action Trail
3. Atomia Identity
4. Atomia Automation Server
5. Atomia Billing

Troubleshooting

1. Make sure that you can connect to your PostgreSQL server with the connection string that is supplied in AddtConnectionString.
2. Install the postgresql-contrib package if you are using Ubuntu.
3. If an Atomia application is not used for a long time, the database connection will be idle and it can be closed by network equipment. You can add Keepalive=30; in the connection strings to prevent closing idle connections. For example, to enable it for the Atomia automation server, the connection string should look like this in the unattended.ini:

    AutomaionServerConnectionString = Server=<your server>;database=atomiaprovisioning2;user id=<your username>;password=<your password>;Keepalive=30;